# 读取Markdown文本内容
with open("1.md", "r", encoding="utf-8") as file:
    lines = file.readlines()

# 遍历Markdown文本行，生成MySQL建表语句
sql_all = ""
table_data = []
for line in lines:
    if "###" in line:
        table_name = line.split(" ")[2].strip().lower()
        table_comment = line.split(" ")[1].strip().lower()

        sql_pre = (
                " DROP TABLE IF EXISTS "
                + table_name
                + " ;CREATE TABLE "
                + table_name
                + " ( "
        )
        sql_suf = (
                " PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '"
                + table_comment
                + "' ROW_FORMAT = DYNAMIC;"
        )
        if '_tj' in table_name:
            for i in range(1, 4):
                table_data.append(
                    [sql_pre.replace(table_name, table_name + str(i)),
                     sql_suf.replace(table_comment, table_comment + str(i))])

        table_data.append([sql_pre, sql_suf])
table_data2 = []
table_data_sub = ""
temp_name = ''
for line in lines:
    if "###" in line:
        t_name = line.split(" ")[2].strip().lower()
        if t_name == temp_name:
            a=1
        else:
            if table_data_sub:
                if '_tj' in temp_name:
                    table_data2.append(table_data_sub)
                    table_data2.append(table_data_sub)
                    table_data2.append(table_data_sub)
                table_data2.append(table_data_sub)
            table_data_sub = ''
            # 标记移动到这里，上面判断的是上次是否为统计表_tj
            temp_name = t_name


    elif "|" in line and "字段" not in line:
        cells = [cell.strip() for cell in line.strip().split("|")]
        if cells[1] == "id":
            table_data_sub += " id bigint NOT NULL AUTO_INCREMENT COMMENT 'id', "
        elif cells[2] == "dateTime":
            table_data_sub += (
                    " "
                    + cells[1]
                    + " datetime NULL COMMENT '"
                    + (cells[4] if cells[4] else cells[3])
                    + "', "
            )
        elif cells[2] == "text":
            table_data_sub += (
                    " "
                    + cells[1]
                    + " text NULL COMMENT '"
                    + (cells[4] if cells[4] else cells[3])
                    + "', "
            )
        elif cells[2] == "bigint":
            table_data_sub += (
                    " "
                    + cells[1]
                    + " bigint NULL COMMENT '"
                    + (cells[4] if cells[4] else cells[3])
                    + "', "
            )
        elif cells[2] == "varchar":
            table_data_sub += (
                    " "
                    + cells[1]
                    + " varchar("
                    + cells[3]
                    + ") NULL COMMENT '"
                    + (cells[4] if cells[4] else cells[3])
                    + "', "
            )
        elif cells[2] == "number":
            table_data_sub += (
                    " "
                    + cells[1]
                    + " numeric("
                    + cells[3]
                    + ") NULL COMMENT '"
                    + (cells[4] if cells[4] else cells[3])
                    + "', "
            )
        else:
            aaa = 2
if '_tj' in table_data[len(table_data)-1][0]:
    table_data2.append(table_data_sub)
    table_data2.append(table_data_sub)
    table_data2.append(table_data_sub)
    table_data2.append(table_data_sub)
else:
    table_data2.append(table_data_sub)

for i in range(len(table_data)):
    sql_all  += table_data[i][0] +  table_data2[i] + table_data[i][1] + "\n"

# 打开一个文件用于写入，如果文件不存在则会创建新文件
with open('1.sql', 'w', encoding="utf-8") as file:
    # 要写入文件的字符串,将字符串写入文件
    file.write(sql_all)

print("String has been written to file.")